﻿/*************************************************************
 * Software of blackcore
 *
 * CLRVersion:4.0.30319.586
 * MachineName:SPACE
 * Author:Andy Huang
 * Email:itblackhole@gmail.com
 * DateTime：6/25/2013 5:20:49 PM
 * Function：
 *************************************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SobFS.Common.Database
{
    public class PagingHelper
    {
        #region ******************************Private Property******************************
        #endregion ******************************Private Property******************************


        #region ******************************Public Property******************************
        #endregion ******************************Public Property******************************


        #region ******************************Structure******************************
        #endregion  ******************************Structure******************************


        #region ******************************Private Method******************************
        #endregion ******************************Private Method******************************


        #region ******************************Public Method******************************
        /// <summary>
        /// 获取分页SQL语句，排序字段需要构成唯一记录
        /// </summary>
        /// <param name="_recordCount">记录总数</param>
        /// <param name="_pageSize">每页记录数</param>
        /// <param name="_pageIndex">当前页数</param>
        /// <param name="_safeSql">SQL查询语句</param>
        /// <param name="_orderField">排序字段，多个则用“,”隔开</param>
        /// <returns>分页SQL语句</returns>
        public static string CreatePagingSql(int recordCount, int pageSize, int pageIndex, string safeSql, string orderField)
        {
            //重新组合排序字段，防止有错误
            string[] arrStrOrders = orderField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            StringBuilder sbOriginalOrder = new StringBuilder(); //原排序字段
            StringBuilder sbReverseOrder = new StringBuilder(); //与原排序字段相反，用于分页
            for (int i = 0; i < arrStrOrders.Length; i++)
            {
                arrStrOrders[i] = arrStrOrders[i].Trim();  //去除前后空格
                if (i != 0)
                {
                    sbOriginalOrder.Append(", ");
                    sbReverseOrder.Append(", ");
                }
                sbOriginalOrder.Append(arrStrOrders[i]);

                int index = arrStrOrders[i].IndexOf(" "); //判断是否有升降标识
                if (index > 0)
                {
                    //替换升降标识，分页所需
                    bool flag = arrStrOrders[i].IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) != -1;
                    sbReverseOrder.AppendFormat("{0} {1}", arrStrOrders[i].Remove(index), flag ? "ASC" : "DESC");
                }
                else
                {
                    sbReverseOrder.AppendFormat("{0} DESC", arrStrOrders[i]);
                }
            }

            //计算总页数
            pageSize = pageSize == 0 ? recordCount : pageSize;
            int pageCount = (recordCount + pageSize - 1) / pageSize;

            //检查当前页数
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            else if (pageIndex > pageCount)
            {
                pageIndex = pageCount;
            }

            StringBuilder sbSql = new StringBuilder();
            //第一页时，直接使用TOP n，而不进行分页查询
            if (pageIndex == 1)
            {
                sbSql.AppendFormat(" SELECT * FROM ({0}) AS T ORDER BY {1} LIMIT {2}", safeSql, sbOriginalOrder.ToString(), pageSize);
            }
            //最后一页时，减少一个TOP
            else if (pageIndex == pageCount)
            {
                sbSql.Append("SELECT * FROM (");
                sbSql.AppendFormat(" SELECT * FROM ({0}) AS T ORDER BY {1} LIMIT {2}) AS T ", safeSql, sbReverseOrder.ToString(), recordCount - pageSize * (pageIndex - 1));
                sbSql.AppendFormat(" ORDER BY {0}", sbOriginalOrder.ToString());
            }
            //前半页数时的分页
            else if (pageIndex <= (pageCount / 2 + pageCount % 2) + 1)
            {
                sbSql.Append("SELECT * FROM");
                sbSql.Append(" (");

                sbSql.Append(" SELECT * FROM");
                sbSql.Append(" (");
                sbSql.Append(" SELECT * FROM (").Append(safeSql).Append(")");
                sbSql.Append(" AS T ORDER BY ").Append(sbOriginalOrder.ToString());
                sbSql.Append(" LIMIT ").Append(pageSize * pageIndex);
                sbSql.Append(" )");
                sbSql.Append(" AS T ORDER BY ").AppendLine(sbReverseOrder.ToString());
                sbSql.Append(" LIMIT ").Append(pageSize);

                sbSql.Append(" )");
                sbSql.Append(" AS T ORDER BY ").AppendLine(sbOriginalOrder.ToString());
            }
            //后半页数时的分页
            else
            {
                sbSql.Append(" SELECT * FROM (");
                sbSql.AppendFormat(" SELECT * FROM ({0}) AS T", safeSql);
                sbSql.AppendFormat(" ORDER BY {0} LIMIT {1}", sbReverseOrder.ToString(), ((recordCount % pageSize) + pageSize * (pageCount - pageIndex) + 1));
                sbSql.AppendFormat(" ) AS T ORDER BY {0} LIMIT {1}", sbOriginalOrder.ToString(), pageSize);
            }
            return sbSql.ToString();
        }

        /// <summary>
        /// 获取记录总数SQL语句
        /// </summary>
        /// <param name="_n">限定记录数</param>
        /// <param name="_safeSql">SQL查询语句</param>
        /// <returns>记录总数SQL语句</returns>
        public static string CreateTopnSql(int limit, string safeSql)
        {
            return string.Format(" SELECT * FROM ({0}) AS T LIMIT {1} ", safeSql, limit);
        }

        /// <summary>
        /// 获取记录总数SQL语句
        /// </summary>
        /// <param name="_safeSql">SQL查询语句</param>
        /// <returns>记录总数SQL语句</returns>
        public static string CreateCountingSql(string safeSql)
        {
            return string.Format(" SELECT COUNT(1) AS RecordCount FROM ({0}) AS T ", safeSql);
        }
        #endregion ******************************Public Method******************************
    }
}
